﻿CREATE PROCEDURE [dbo].[ETL_SP_GetTaskList]
	@categoryId uniqueidentifier = null,
    @filter nvarchar(32) = null,
	@start int,
	@limit int,
	@total int output
AS
if @filter is null or @filter=''
begin
	Select V.*
	From(	
		SELECT 
			ROW_NUMBER() OVER(Order BY T.CreatedTime) as _RowIndex,
			T.*,
			(SELECT Max(CreatedTime) FROM ETL_TaskExecuteRecord WHERE TaskId = T.Id) as LastExecutedTime,
			DSER.Name as DtSerName,
			DS.Name as DtSourceName,
			(select DT.Name from ETL_DtTable as DT where DT.Id = DSM.DtTableId) as DtTableName
		FROM ETL_Task as T
			INNER JOIN ETL_DtSer as DSER ON DSER.Id = T.DtSerId
			INNER JOIN ETL_DtSource as DS ON DS.Id = T.DtSourceId
			LEFT JOIN ETL_DtSourceMapping as DSM on DSM.DtSourceId = T.DtSourceId
		WHERE T.IsDeleted = 0
			and (@categoryId is null or T.CategoryId = @categoryId)) as V
	where V._RowIndex > @start and V._RowIndex <= @start + @limit

	set @total = (SELECT COUNT(T.Id)
		FROM ETL_Task as T
		WHERE T.IsDeleted = 0
			and (@categoryId is null or T.CategoryId = @categoryId))
end
else
begin
	Select V.*
	From(	
		SELECT 
			ROW_NUMBER() OVER(Order BY T.CreatedTime) as _RowIndex,
			T.*,
			(SELECT Max(CreatedTime) FROM ETL_TaskExecuteRecord WHERE TaskId = T.Id) as LastExecutedTime,
			DSER.Name as DtSerName,
			DS.Name as DtSourceName,
			(select DT.Name from ETL_DtTable as DT where DT.Id = DSM.DtTableId) as DtTableName
		FROM ETL_Task as T
			INNER JOIN ETL_DtSer as DSER ON DSER.Id = T.DtSerId
			INNER JOIN ETL_DtSource as DS ON DS.Id = T.DtSourceId
			LEFT JOIN ETL_DtSourceMapping as DSM on DSM.DtSourceId = T.DtSourceId
		WHERE T.IsDeleted = 0 and (T.Name like @filter or DSER.Name like @filter or DS.Name like @filter)
			and (@categoryId is null or T.CategoryId = @categoryId)) as V
	where V._RowIndex > @start and V._RowIndex <= @start + @limit

	set @total = (SELECT COUNT(T.Id)
		FROM ETL_Task as T
			INNER JOIN ETL_DtSer as DSER ON DSER.Id = T.DtSerId
			INNER JOIN ETL_DtSource as DS ON DS.Id = T.DtSourceId
		WHERE T.IsDeleted = 0 and (T.Name like @filter or DSER.Name like @filter or DS.Name like @filter)
			and (@categoryId is null or T.CategoryId = @categoryId))
end
		
RETURN 0
